package vip.xiaonuo.modular.databaseinfo.service.impl;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import vip.xiaonuo.core.annotion.BusinessLog;
import vip.xiaonuo.core.annotion.Permission;
import vip.xiaonuo.core.enums.LogAnnotionOpTypeEnum;
import vip.xiaonuo.core.pojo.page.PageResult;
import vip.xiaonuo.modular.databaseinfo.entity.DatabaseInfo;
import vip.xiaonuo.modular.databaseinfo.param.DatabaseInfoParam;
import vip.xiaonuo.modular.tablefield.entity.TableField;
import vip.xiaonuo.modular.tableinfo.entity.TableInfo;
import vip.xiaonuo.modular.tablefield.param.TableFieldParam;

import java.sql.*;
import java.text.MessageFormat;
import java.util.*;
import java.util.function.Function;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import vip.xiaonuo.modular.databaseinfo.controller.databaseTableFiled;


public class MySQLTableInfoUtil extends AbstractDBTableInfoUtil {

    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String TABLE_SCHEMA = "TABLE_SCHEMA";
    private static final String CREATE_TIME = "CREATE_TIME";
    private static final String UPDATE_TIME = "UPDATE_TIME";
    private static final String TABLE_COMMENT = "TABLE_COMMENT";

    public MySQLTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // 指定系统 schema
        excludeSchema = "('information_schema')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.MYSQL;
    }

    // 获取表的总数的SQL
    @Override
    protected String getTableCountSQL() {
        // 在 使用 MessageFormat 时，一定要注意：若字符串需要使用单引号(')时，必须连续写两个，否则，将在 format 时，将会被去掉
        // 即，如果写成 'T'，那么，最终结果是 T，如果是 ''T''，最终结果才是 'T'
        // 不过，作为参数传给 format 时，只需要写一个 ' 即可，如上面的 excludeSchema 的值
        String sql = MessageFormat.format("SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN {0} AND TABLE_TYPE=''BASE TABLE''", excludeSchema);
        System.out.println("[DEBUG] sql = " + sql);
        return sql;
    }

    // 获取指定数量的 表的集合，即分页
    @Override
    @Permission
    @BusinessLog(title = "数据库信息表_导出", opType = LogAnnotionOpTypeEnum.QUERY)
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        // mysql的版本需要在 5.7 以上，否则，create_time 和 update_time 可能返回NULL
//        System.err.println("For mysql, make sure the server which you connect to is of version >= 5.7.x");
//        System.err.println("If not, the CREATE_TIME and UPDATE_TIME maybe return NULL");

//        List tableProperties = new ArrayList();
        List<TableFieldParam> tableFiledProperties= new ArrayList();
        List<TableInfo> TableInfoParamProperties= new ArrayList<TableInfo>();
        Collection<TableField> tableFiledPropertiess = new ArrayList<TableField>();
        Collection<TableInfo> TableInfoPropertiess = new ArrayList<TableInfo>();
        List<databaseTableFiled> databaseTableFiledProperties= new ArrayList();
        databaseTableFiled databaseTableFiled = new databaseTableFiled();
        final int offset = (pageNumber - 1) * pageSize;
        final int limit = pageSize;

        // 把字段名作成了参数({0} ~ {4})传了进去，这样做是为了保证在下面的 ResultSet get时，可以直接使用 列名，而不是使用 index
//        String sql = MessageFormat.format("" +
//                        "SELECT {0}, {1}, {2}, {3}, {4} FROM INFORMATION_SCHEMA.TABLES " +
//                        "WHERE TABLE_SCHEMA NOT IN {5} AND TABLE_TYPE=''BASE TABLE'' " +
//                        "ORDER BY {1} LIMIT ? OFFSET ?",
//                TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME, excludeSchema);
        String dbName =dataSource.dbName;
        Long databaseId = dataSource.databaseId;
        Properties connectionProps = new Properties();
        connectionProps.put("user", dataSource.username);
        connectionProps.put("password", dataSource.password);
        connectionProps.put("remarks", "true");
        connectionProps.put("useInformationSchema", "true");
        String tableCountSql = "SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = '"+dbName+"' GROUP BY table_schema";

//        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password,connectionProps);
        try {
            Connection connection = DriverManager.getConnection(dataSource.url, connectionProps);
            Statement Statement = connection.createStatement();
            System.out.println(tableCountSql);
            ResultSet tableCountRs = Statement.executeQuery(tableCountSql);
            String tableSum = "";
            //数据库内表的个数
            while (tableCountRs.next()){
                tableSum = tableCountRs.getString("TABLES");
            }
            DatabaseMetaData dbmd = connection.getMetaData();
            ResultSet resultSetTable = dbmd.getTables(connection.getCatalog(), connection.getCatalog(), "%", new String[]{"TABLE"});
            while (resultSetTable.next()) {
                //cloumn_catalog_name
                String tableName = resultSetTable.getString("TABLE_NAME");
                String filedCountSql = "SELECT count(*) filedCount FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = '"+dbName+"' AND TABLE_NAME = '"+tableName+"'";
                String filedCount = "";
                ResultSet filedCountRs = Statement.executeQuery(filedCountSql);
                while(filedCountRs.next()){
                    filedCount = filedCountRs.getString("filedCount");
                }
                String tableCupSql = "SELECT CREATE_TIME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= '" + dbName + "' and TABLE_NAME = '"+tableName+"'";
                //数据库表的创建时间和更新时间
                ResultSet tableCupRs = Statement.executeQuery(tableCupSql);
                String tableCreateTime = "";
                String tableUpdateTime = "";
                while (tableCupRs.next()){
                    //table_create_time
                    tableCreateTime = tableCupRs.getString("CREATE_TIME");
                    //table_update_time
                    tableUpdateTime = tableCupRs.getString("UPDATE_TIME");
                }
                //数据库表的索引
                String indexTableSql = "show index from "+tableName;

                String indexTableKeyName = "";
                String indexTableColumnName = "";

                ResultSet indexTableRs = Statement.executeQuery(indexTableSql);
                while(indexTableRs.next()){
                    indexTableKeyName = indexTableRs.getString("Key_name");
                    indexTableColumnName = indexTableRs.getString("Column_name");
                }

                //table_cn_name
                String tableCnName = resultSetTable.getString("REMARKS");
//                System.out.println(tableCnName);
                //table_schema
//                String tableSchem = resultSetTable.getString("TABLE_SCHEM");
                String tableSchem = dbName;
//                System.out.println(tableSchem);
                //
                String TABLE_CAT = resultSetTable.getString("TABLE_CAT");
//                System.out.println(TABLE_CAT);
                //table_type
                String tableType = resultSetTable.getString("TABLE_TYPE");
//                System.out.println(tableType);

                ResultSet rssPrimary = dbmd.getPrimaryKeys(null, null, tableName);
                String primaryColumn  = "";
                Integer primarysum = 0;
                while (rssPrimary.next()) {
                    primaryColumn  = rssPrimary.getString("COLUMN_NAME");
                    primarysum++;
                }

                //tableInfo
                TableInfo TableInfoParam = new TableInfo();
                TableInfoParam.setTableCnName(tableCnName);
                TableInfoParam.setTableEnName(tableName);
                TableInfoParam.setTableSchema(tableSchem);
                TableInfoParam.setTableType(tableType);
                TableInfoParam.setDbName(dbName);
                TableInfoParam.setDatabaseId(databaseId);
                TableInfoParam.setTableSum(tableSum);
                TableInfoParam.setTableCreateTime(tableCreateTime);
                TableInfoParam.setTableUpdateTime(tableUpdateTime);
                TableInfoParam.setTableFieldSum(filedCount);
                TableInfoPropertiess.add(TableInfoParam);
                ResultSet rss = dbmd.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, "%");

                while (rss.next()) {
//                    DatabaseInfoParam DatabaseInfoParam = new DatabaseInfoParam();
                    //column_name
                    String columnName = "";
                    columnName = rss.getString("COLUMN_NAME");
//                    System.out.println(columnName);
                    //cloumn_remarks
                    String cloumnRemarks = rss.getString("REMARKS");
//                    System.out.println(cloumnRemarks);
                    //cloumn_type
                    String columnType = rss.getString("TYPE_NAME");
//                    System.out.println(columnType);
                    //cloumn_length
                    Integer cloumnLength = rss.getInt("COLUMN_SIZE");
//                    System.out.println(cloumnLength);
                    //cloumn_index
                    String cloumnIndex = rss.getString("ORDINAL_POSITION");
//                    System.out.println(cloumnIndex);
                    //cloumn_scale
                    Integer cloumnScale = rss.getInt("DECIMAL_DIGITS");
//                    System.out.println(cloumnScale);
                    //cloumn_is_auto
                    String cloumnIsAuto = "";
                    if(rss.getString("IS_AUTOINCREMENT").equals("YES")){
                        cloumnIsAuto ="是";
                    }else if(rss.getString("IS_AUTOINCREMENT").equals("NO")){
                        cloumnIsAuto ="否";
                    }
                    //cloumn_is_primary
                    String cloumnIsPrimary = "否";
                    if (columnName.equals(primaryColumn)) {
                        if(primarysum>1){
                            cloumnIsPrimary = "联合主键";
                        }else{
                            cloumnIsPrimary = "是";
                        }
                    }

                    //cloumn_is_nullable
                    String cloumnIsNullable = "否";
                    if(rss.getString("IS_NULLABLE").equals("YES")){
                        cloumnIsNullable ="是";
                    }else if(rss.getString("IS_NULLABLE").equals("NO")){
                        cloumnIsNullable ="否";
                    }

                    //tableFiled
//                    TableFieldParam TableFieldParam = new TableFieldParam();
                    TableField TableFieldParam = new TableField();
                    TableFieldParam.setColumnName(columnName);
                    TableFieldParam.setCloumnRemarks(cloumnRemarks);
                    TableFieldParam.setCloumnType(columnType);
                    TableFieldParam.setCloumnLength(String.valueOf(cloumnLength));
                    TableFieldParam.setCloumnScale(String.valueOf(cloumnScale));
                    TableFieldParam.setCloumnIsAuto(cloumnIsAuto);
                    TableFieldParam.setCloumnIsPrimary(cloumnIsPrimary);
                    TableFieldParam.setCloumnIsNullable(cloumnIsNullable);
                    TableFieldParam.setCloumnCatalogName(tableName);
                    TableFieldParam.setCloumnIsCurrency("否");
                    TableFieldParam.setCloumnIsSearchable("是");
                    TableFieldParam.setCloumnIsReadonly("否");
                    TableFieldParam.setPermissions("中级");
                    if(columnName.equals(indexTableColumnName)){
                        TableFieldParam.setCloumnIndex(indexTableKeyName);
                    }else{
                        TableFieldParam.setCloumnIndex("无");
                    }
                    TableFieldParam.setDatabaseId(String.valueOf(databaseId));
                    tableFiledPropertiess.add(TableFieldParam);
                }
            }
        } catch (Exception e) {
            System.err.println("[ERROR] MySQLTableInfoUtil->getTableInfoList : " + e.toString());
        }
        dataSource.TableInfoService.saveBatch(TableInfoPropertiess);
        dataSource.TableFieldService.saveBatch(tableFiledPropertiess);
        return databaseTableFiledProperties;
    }

    @Override
    public PageResult<DatabaseInfo> page(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public List<DatabaseInfo> list(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public void add(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public void delete(List<DatabaseInfoParam> databaseInfoParamList) {

    }

    @Override
    public void edit(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public DatabaseInfo detail(DatabaseInfoParam databaseInfoParam) {
        return null;
    }

    @Override
    public void export(DatabaseInfoParam databaseInfoParam) {

    }

    @Override
    public List<TableInfo> queryNum(String tio) {
        return null;
    }

    @Override
    public boolean saveBatch(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean saveOrUpdateBatch(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean updateBatchById(Collection<DatabaseInfo> entityList, int batchSize) {
        return false;
    }

    @Override
    public boolean saveOrUpdate(DatabaseInfo entity) {
        return false;
    }

    @Override
    public DatabaseInfo getOne(Wrapper<DatabaseInfo> queryWrapper, boolean throwEx) {
        return null;
    }

    @Override
    public Map<String, Object> getMap(Wrapper<DatabaseInfo> queryWrapper) {
        return null;
    }

    @Override
    public <V> V getObj(Wrapper<DatabaseInfo> queryWrapper, Function<? super Object, V> mapper) {
        return null;
    }

    @Override
    public BaseMapper<DatabaseInfo> getBaseMapper() {
        return null;
    }

    @Override
    public Class<DatabaseInfo> getEntityClass() {
        return null;
    }
}
